package dao;

import bean.Hou;
import bean.Result;
import service.UserServiceImpl;
import utils.DBUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * @author 李夏
 * @version 1.0
 */
public class HouInfoMapper {
    //获取宿管总数
    public static Integer queryHouAllCount() throws SQLException {
        Connection connection = DBUtil.getConnection();
        ResultSet resultSet = null;
        PreparedStatement preparedStatement = null;
        String sql = "select count(*) as total_hou  \n"+
                "from hou  \n"+
                "where delete_flag=0";
        preparedStatement = connection.prepareStatement(sql);
        resultSet = preparedStatement.executeQuery();
        Integer count = null;
        if (resultSet.next()) {
            count = resultSet.getInt("total_hou");
        }

        DBUtil.close(connection,preparedStatement,resultSet);


        return count;
    }
//按照宿管id或者宿管名字模糊查询
    public static List<Hou> getHouIdName(Integer houId, String houName) throws SQLException {

            Connection connection = DBUtil.getConnection();
            PreparedStatement preparedStatement = null;
            ResultSet resultSet = null;
            List<Hou> hous = new ArrayList<>();
            try {
                StringBuilder sqlBuilder = new StringBuilder("SELECT * FROM hou WHERE 1=1 ");
                List<Object> parameters = new ArrayList<>();

                if (houId != null && houId != 0) {
                    sqlBuilder.append(" AND hou_id = ?");
                    parameters.add(houId);
                }

                if (houName != null && !houName.isEmpty()) {
                    sqlBuilder.append(" AND hou_name LIKE ?");
                    parameters.add("%" + houName + "%"); // 这里直接构建LIKE查询的字符串，但请注意SQL注入的风险
                }

                String sql = sqlBuilder.toString();
                preparedStatement = connection.prepareStatement(sql);

                // 设置PreparedStatement的参数
                int parameterIndex = 1;
                for (Object parameter : parameters) {
                    preparedStatement.setObject(parameterIndex++, parameter);
                }

                resultSet = preparedStatement.executeQuery(); // 使用executeQuery()来执行查询

                while (resultSet.next()) { // 使用while循环来处理结果集中的所有行
                    Hou hou = new Hou();
                    hou.setPassword(resultSet.getString("password"));
                    hou.setHouId(resultSet.getInt("hou_id"));
                    hou.setHouName(resultSet.getString("hou_name"));
                    hou.setPhone(resultSet.getString("phone"));
                    hou.setBuildId(resultSet.getInt("build_id"));
                    hou.setEmail(resultSet.getString("email"));
                    hou.setAge(resultSet.getInt("age"));
                    hou.setSex(resultSet.getInt("sex"));
                    hou.setUpdateTime(resultSet.getDate("update_time"));
                    hou.setCreateTime(resultSet.getDate("create_time"));
                    // ... 设置其他字段
                    hous.add(hou);
                }
            } catch (SQLException e) {
                throw new RuntimeException(e);
            } finally {
                DBUtil.close(connection, preparedStatement, resultSet);
            }

            return hous;
        }


    }

